<?php
include_once('config.php');
/*
 * Vino Santo, Sonoma , Ikes Redwood City
 * lp000051403449 , lp267563216881 , lp000051382515
 * 
 * La Viga = lpLaVigaRwc, House of Bagels=lp000051396857, Pronto Rotisserie & Pizzeria = lp000051383792
 * 
 * 'lp000051403449'=>'Vino Santo','lpLaVigaRwc'=>'La Viga','lpMagdaBg'=>'Magda Luna','lpAthertonSubsRwc'=>'Atherton Subs'
 */
session_start();

$MERCHANT = 'lp000051403449'; //  'lpAthertonSubsRwc';
$month    = 3;

if ($month == 1) {
  $_SESSION['from'] = '2013-01-01 03:00:00';
  $_SESSION['to'] = '2013-02-01 02:59:00';
} else if ($month == 2) {
  $_SESSION['from'] = '2013-02-01 03:00:00';
  $_SESSION['to'] = '2013-03-01 02:59:00';
} else if ($month == 3) {
  $_SESSION['from'] = '2013-03-01 03:00:00';
  $_SESSION['to'] = '2013-04-01 02:59:00';
}

// $type = magcard, online
function getTotalSales($MERCHANT, $type = null) {
  $total = 0;
  // CONCAT(TL.TICKET,'-',TL.LINE,'-',TL.MERCHANT_ID,'-',P.TRANSID) AS UNC_KEY
  $q = "SELECT TL.UNITS, TL.PRICE, CONCAT(TL.TICKET,'-',TL.LINE,'-',TL.MERCHANT_ID,'-',P.TRANSID) AS UNC_KEY, TL.CREATEDDATE FROM TICKETLINES TL, PAYMENTS P 
        WHERE TL.MERCHANT_ID=P.MERCHANT_ID AND TL.MERCHANT_ID= '$MERCHANT' AND P.RECEIPT=TL.TICKET AND TL.LINETYPE=0 AND TL.UNITS > 0 
        AND TL.CREATEDDATE > '".$_SESSION['from']."' AND TL.CREATEDDATE <= '".$_SESSION['to']."' AND P.PAYMENT!='magcardrefund'";
  if (!is_null($type)) {
    $q.=" AND P.PAYMENT='$type'";
  }
  $q.=" ORDER BY TL.CREATEDDATE";
  
  $orderArr = array() ;
  $res = mysql_query($q) or die(mysql_error());
  $tmpArr = array();
  while ($row = mysql_fetch_assoc($res)) {
    if (!isset($tmpArr[$row['UNC_KEY']])) {
      $total+= $row['UNITS']*$row['PRICE'];
      $tmpArr[$row['UNC_KEY']] = 1;
      $orderArr[] = $row; 
    } else {
      echo $row['UNC_KEY']."\n";
    }
  }
  
  if (is_null($type)) {
    $_SESSION['to_orders'] = $orderArr;
  }

  return $total;
}

function getOrdersByTime($MERCHANT) {
  $dataOrders = array();
  $q = "SELECT
          T.ID, T.CREATEDDATE
        FROM
          TICKETS T
        WHERE
          T.MERCHANT_ID = '".$MERCHANT."' AND
          T.CREATEDDATE > '".$_SESSION['from']."' AND T.CREATEDDATE <= '".$_SESSION['to']."' AND 
          T.TICKETTYPE = 0
        ORDER BY T.CREATEDDATE
       ";
  $res = mysql_query($q) or die(mysql_error());

  while ($row = mysql_fetch_assoc($res)) {
    $dataOrders[] = $row;
  }
  return $dataOrders;
}

function getMenuSessionSlots($MERCHANT) {
  $menuSession = array();
  $q = "SELECT
          MENUSESSIONS_ID, DAYOFWEEK, FROMTIME, TOTIME
        FROM
          MENUSESSIONSLOTS
        WHERE
          MERCHANT_ID = '".$MERCHANT."' AND
          AVAILABLE = 1
        ORDER BY DAYOFWEEK
       ";

  $res = mysql_query($q) or die(mysql_errno());
  while ($row = mysql_fetch_assoc($res)) {
    $menuSession[] = $row;
  }
  return $menuSession;
}

function getDays() {
  return array(
          1 => 'Sunday',
          2 => 'Monday',
          3 => 'Tuesday',
          4 => 'Wednesday',
          5 => 'Thursday',
          6 => 'Friday',
          7 => 'Saturday'
  );
}

function topTwenty ($MERCHANT, $order, $limit = 20, $orderAs = 'DESC') {
  $dataTopTwByTotal = array();
  
  $q = "SELECT 
          TL.PRODUCT AS PRODUCT_ID,
          SUM(TL.UNITS) AS TOTAL_SALE_COUNT,
          SUM(TL.UNITS * TL.PRICE) AS TOTAL
        FROM
          TICKETLINES TL
        WHERE
          TL.MERCHANT_ID = '".$MERCHANT."' AND
          TL.CREATEDDATE > '".$_SESSION['from']."' AND TL.CREATEDDATE <= '".$_SESSION['to']."' AND
          TL.UNITS > 0 AND
          TL.PRODUCT IS NOT NULL AND
          TL.LINETYPE = 0
        GROUP BY TL.PRODUCT
        ORDER BY ".$order." ".$orderAs."
        LIMIT $limit
       ";

  $res = mysql_query($q) or die(mysql_error().' in topTwenty');
  $i = 1;
  while ($row = mysql_fetch_assoc($res)) {
    $row['RANK'] = $i;
    $dataTopTwByTotal[$row['PRODUCT_ID']] = $row;
    $i++;
  }

  return $dataTopTwByTotal;
}

function getItemSalesByIds($MERCHANT, $proIds) {
  $dataOrders = array();

  $q = "SELECT
          TL.PRODUCT AS PRODUCT_ID,
          SUM(TL.UNITS) AS TOTAL_SALE_COUNT,
          SUM(TL.UNITS * TL.PRICE) AS TOTAL,
          TL.CREATEDDATE
        FROM
          TICKETLINES TL
        WHERE
          TL.MERCHANT_ID = '".$MERCHANT."' AND
          TL.CREATEDDATE > '".$_SESSION['from']."' AND TL.CREATEDDATE <= '".$_SESSION['to']."' AND
          TL.UNITS > 0 AND
          TL.PRODUCT IS NOT NULL AND
          TL.LINETYPE = 0 AND
          TL.PRODUCT IN ('".implode("','", $proIds)."')
        GROUP BY TL.PRODUCT
        ORDER BY TL.CREATEDDATE
       ";

  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $dataOrders[$row['PRODUCT_ID']] = $row;
  }

  return $dataOrders;
}

function getProductByIds($MERCHANT, $proIds) {
  $data = array();

  $q = "SELECT
          ID,NAME,CODE,PRICESELL
        FROM
          PRODUCTS
        WHERE
          MERCHANT_ID = '".$MERCHANT."' AND
          ID IN ('".implode("','", $proIds)."')
       ";

  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $data[$row['ID']] = $row;
  }

  return $data;
}

function getAllOrderCount($MERCHANT) {
  $dataTopTwByTotal = array();
  $q = "SELECT
          SUM(TL.UNITS) AS TOTAL_SALE_COUNT,
          SUM(TL.UNITS * TL.PRICE) AS TOTAL
        FROM
          TICKETLINES TL
        WHERE
          TL.MERCHANT_ID = '".$MERCHANT."' AND
          TL.CREATEDDATE > '".$_SESSION['from']."' AND TL.CREATEDDATE <= '".$_SESSION['to']."' AND
          TL.UNITS > 0 AND
          TL.LINETYPE = 0
       ";

  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $dataTopTwByTotal[] = $row;
  }

  return $dataTopTwByTotal[0];
}


function getLessOrders($MERCHANT,$limit = 20, $orderBy = "TOTAL_SALE_COUNT", $asc = 1) {
  $dataOrders = array();

  /* $q = "SELECT
          TL.PRODUCT AS PRODUCT_ID,
          SUM(TL.UNITS) AS TOTAL_SALE_COUNT,
          SUM(TL.UNITS * TL.PRICE) AS TOTAL,
          TL.PRICE
        FROM
          TICKETLINES TL
        WHERE
          TL.MERCHANT_ID = '".$MERCHANT."' AND
          TL.CREATEDDATE > '".$_SESSION['from']."' AND TL.CREATEDDATE <= '".$_SESSION['to']."' AND
          TL.UNITS > 0 AND
          TL.PRODUCT IS NOT NULL AND
          TL.LINETYPE = 0
        GROUP BY TL.PRODUCT
        ORDER BY TOTAL_SALE_COUNT, TOTAL
        limit 20
       "; */
  
  $q = "SELECT
          TL.PRODUCT AS PRODUCT_ID,
          UNITS,
          TL.PRICE
        FROM
          TICKETLINES TL
        WHERE
          TL.MERCHANT_ID = '".$MERCHANT."' AND
          TL.CREATEDDATE > '".$_SESSION['from']."' AND TL.CREATEDDATE <= '".$_SESSION['to']."' AND
          TL.UNITS > 0 AND
          TL.PRODUCT IS NOT NULL AND
          TL.LINETYPE = 0
       ";

  $res = mysql_query($q) or die(mysql_error());
  echo "query ok \n";
  
  $tmpArr = array();
  while ($row = mysql_fetch_assoc($res)) {
    $total = $row['PRICE']*$row['UNITS'];
    if (isset($dataOrders[$row['PRODUCT_ID']])) {
      $dataOrders[$row['PRODUCT_ID']]['TOTAL_SALE_COUNT'] = $dataOrders[$row['PRODUCT_ID']]['TOTAL_SALE_COUNT']+$row['UNITS'];
      $dataOrders[$row['PRODUCT_ID']]['TOTAL'] = $dataOrders[$row['PRODUCT_ID']]['TOTAL']+$total;
    } else {
      $dataOrders[$row['PRODUCT_ID']] = array(
                                          'PRODUCT_ID' => $row['PRODUCT_ID'],
                                          'TOTAL_SALE_COUNT'  => $row['UNITS'],
                                          'TOTAL'   => $total,
                                          'PRICE'   => $row['PRICE']);
    }
    
    // keep item count
    if ($orderBy == "TOTAL_SALE_COUNT") {
      $tmpArr[$row['PRODUCT_ID']] = isset($tmpArr[$row['PRODUCT_ID']]) ? ($tmpArr[$row['PRODUCT_ID']]+$row['UNITS']) : $row['UNITS'];
    } else if ($orderBy == 'TOTAL') {
      $tmpArr[$row['PRODUCT_ID']] = isset($tmpArr[$row['PRODUCT_ID']]) ? ($tmpArr[$row['PRODUCT_ID']]+$total) : $total;
    }
  }
  if ($asc == 1) {
    echo "array order \n";
    asort($tmpArr);
  } else if ($asc == 2) {
    echo "array reverse order \n";
    arsort($tmpArr);
  }
  $tmpArr1 = array_slice($tmpArr, 0, $limit);
  
  $finalData = array();
  $i = 1;
  foreach ($tmpArr1 as $k => $arr) {
    $dataOrders[$k]['RANK'] = $i;
    $finalData[$k] = $dataOrders[$k];
    $i++;
  }
  
  return $finalData;
}

function getNoSoldItems($MERCHANT) {
  $dataOrders = array();
  $ids = array();
  
  $q = "SELECT
          PRODUCT
        FROM
          TICKETLINES
        WHERE 
          MERCHANT_ID = '".$MERCHANT."' AND
          CREATEDDATE > '".$_SESSION['from']."' AND CREATEDDATE <= '".$_SESSION['to']."' AND
          UNITS > 0 AND
          PRODUCT IS NOT NULL AND
          LINETYPE = 0
        GROUP BY PRODUCT
        ";

  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $ids[] = $row['PRODUCT'];
  }
  
  
  $q = "SELECT
          P.ID AS PRODUCT_ID, P.NAME, P.CODE, P.PRICESELL,
          0 AS TOTAL_SALE_COUNT,
          0 AS TOTAL
        FROM
          PRODUCTS P,
          PRODUCTS_CAT PC
        WHERE
          P.ID NOT IN ('".implode("','", $ids)."') AND 
          P.MERCHANT_ID = '".$MERCHANT."' AND
          P.DELETED = 0 AND
          P.SPECIAL_TYPE IS NULL AND
          P.ID = PC.PRODUCT AND
          P.MERCHANT_ID=PC.MERCHANT_ID
       ";

  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $dataOrders[$row['PRODUCT_ID']] = $row;
  }

  return $dataOrders;
}


function getLastOrderDates($MERCHANT, $proIds) {
  $days = array();
  $prodIds = array();
  $exProdIds = array();
  $q = "SELECT
          TL.PRODUCT AS PRODUCT_ID, CREATEDDATE, DATEDIFF('".$_SESSION['to']."', CREATEDDATE) AS DAYS
        FROM
          TICKETLINES TL
        WHERE
          TL.MERCHANT_ID = '".$MERCHANT."' AND
          TL.PRODUCT IN ('".implode("','", $proIds)."') AND 
          TL.CREATEDDATE BETWEEN '2010-01-01' AND '".$_SESSION['to']."' AND
          TL.UNITS > 0 AND
          TL.PRODUCT IS NOT NULL AND
          TL.LINETYPE = 0
        ORDER BY DAYS
       ";

  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    if (!isset($days[$row['PRODUCT_ID']]['DAYS']) || ($days[$row['PRODUCT_ID']]['DAYS'] > $row['DAYS']) ) {
      if ($row['DAYS'] >= 7) {
        $days[$row['PRODUCT_ID']]['DAYS'] = $row['DAYS'];
        $days[$row['PRODUCT_ID']]['LAST'] = $row['CREATEDDATE'];
        $prodIds[$row['PRODUCT_ID']] = $row['DAYS'];
      } else {
        $exProdIds[] = $row['PRODUCT_ID'];
      }
    }
    if (isset($days[$row['PRODUCT_ID']]['COUNT'])) {
      $days[$row['PRODUCT_ID']]['COUNT'] = $days[$row['PRODUCT_ID']]['COUNT']+1;
    } else {
      $days[$row['PRODUCT_ID']]['COUNT'] = 1;
    }

  }
  foreach ($prodIds as $pid => $cnt) {
    if (in_array($pid, $exProdIds)) {
      unset($prodIds[$pid]);
    }
  }

  return $prodIds;
}

function trends($MERCHANT, $ORDER) {
  $dataTopTwByTotal = array();
  
  $q = "SELECT
          P.ID AS PRODUCT_ID, P.NAME, P.CODE, P.PRICESELL,
          SUM(TL.UNITS) AS TOTAL_SALE_COUNT,
          SUM(TL.UNITS * TL.PRICE) AS TOTAL
        FROM
          TICKETLINES TL
          LEFT JOIN PRODUCTS P ON TL.MERCHANT_ID = P.MERCHANT_ID AND TL.PRODUCT = P.ID
        WHERE
          TL.MERCHANT_ID = '".$MERCHANT."' AND
          TL.CREATEDDATE > '".$_SESSION['from']."' AND TL.CREATEDDATE <= '".$_SESSION['to']."' AND
          TL.UNITS > 0 AND
          TL.PRODUCT IS NOT NULL AND
          TL.LINETYPE = 0
        GROUP BY TL.PRODUCT
        ORDER BY ".$ORDER." DESC
       ";

  $res = mysql_query($q) or die(mysql_errno());
  while ($row = mysql_fetch_assoc($res)) {
    $dataTopTwByTotal[$row['PRODUCT_ID']] = $row;
  }

  return $dataTopTwByTotal;
}

function getTotalSalesWithTax($MERCHANT, $type = null) {
  $total = 0;
  
  $q = "SELECT SUM(P.TOTAL) AS TOT_AMOUNT,DATENEW FROM PAYMENTS P,RECEIPTS R WHERE P.MERCHANT_ID = '".$MERCHANT."' AND R.ID=P.RECEIPT AND P.MERCHANT_ID=R.MERCHANT_ID AND DATENEW >'".$_SESSION['from']."' AND DATENEW < '".$_SESSION['to']."' AND TOTAL > 0";
  if (!is_null($type)) {
    $q.=" AND P.PAYMENT='$type'";
  }
  $q.= " GROUP BY R.ID ";
  $q.= " ORDER BY R.DATENEW";
  
  $orderArr = array() ;
  $res = mysql_query($q) or die(mysql_error());
  $tmpArr = array();
  while ($row = mysql_fetch_assoc($res)) {
    $total+= $row['TOT_AMOUNT'];
    $orderArr[] = $row; 
  }
  
  if (is_null($type)) {
    $_SESSION['to_orders'] = $orderArr;
  }

  return $total;
}

function getTotalTaxes($MERCHANT) {
  $total = 0;
  
  // $q = "SELECT SUM(T.AMOUNT) AS TOT_AMOUNT FROM TAXLINES T,RECEIPTS R WHERE T.MERCHANT_ID = '".$MERCHANT."' AND R.ID=T.RECEIPT AND T.MERCHANT_ID=R.MERCHANT_ID AND DATENEW >'".$_SESSION['from']."' AND DATENEW < '".$_SESSION['to']."' AND AMOUNT > 0";
  // $q.= " GROUP BY R.ID ";
  $q = "SELECT SUM(T.AMOUNT) AS TOT_AMOUNT FROM TAXLINES T WHERE T.MERCHANT_ID = '".$MERCHANT."' AND T.CREATEDDATE >'".$_SESSION['from']."' AND T.CREATEDDATE < '".$_SESSION['to']."' AND T.AMOUNT > 0";
  
  $orderArr = array() ;
  $res = mysql_query($q) or die(mysql_error());
  $tmpArr = array();
  while ($row = mysql_fetch_assoc($res)) {
    $total+= $row['TOT_AMOUNT'];
    $orderArr[] = $row; 
  }
  
  return $total;
}

function getAmountNoTax($amount, $rate) {
  return $amount * (1 - $rate);
}

function getTotalUnits($MERCHANT) {
  $q= "SELECT SUM(UNITS) AS TOTAL_SALE_COUNT FROM TICKETLINES WHERE MERCHANT_ID = '".$MERCHANT."' AND 
          CREATEDDATE > '".$_SESSION['from']."' AND CREATEDDATE <= '".$_SESSION['to']."' AND
          UNITS > 0 AND
          PRODUCT IS NOT NULL AND
          LINETYPE = 0";
  
  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $totUnit = $row['TOTAL_SALE_COUNT']; 
  }
  return $totUnit;
  
}

function getMenuItems1111($MERCHANT) {
  $menu = array();
  $cat = array();
  $q = "SELECT ID,NAME FROM CATEGORIES WHERE MERCHANT_ID = '$MERCHANT'";
  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $cat[$row['ID']] = $row['NAME'];
  }
  
  $q = "SELECT P.CODE, P.OLO_NAME as PNAME, P.ATTRIBUTES as PDESC, C.NAME as CNAME, C.DESCRIPTION as CDESC, P.ID as PID, C.PARENTID, C.ID as CAT_ID 
          FROM 
        PRODUCTS P,CATEGORIES C 
          WHERE 
        P.MERCHANT_ID = C.MERCHANT_ID AND P.CATEGORY=C.ID AND P.MERCHANT_ID = '$MERCHANT' AND C.OLO_VISIBLE = 1";
  
  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    if (!is_null($row['PARENTID'])) {
      $row['CNAME'] = $cat[$row['PARENTID']];
    }
    $menu[$row['PID']] = $row;
  }
  return $menu;
}


function getMenuItems($MERCHANT) {
  $menu = array();
  $q = "SELECT ID,NAME,  DESCRIPTION AS DESCR FROM CATEGORIES WHERE MERCHANT_ID = '$MERCHANT' AND PARENTID IS NULL AND OLO_VISIBLE=1 ORDER BY OLO_LINENO";
  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $menu[$row['ID']]['MAIN_CAT'] = $row['NAME'];
    $cat[$row['ID']] = $row['NAME'];
  }
  
  $q = "SELECT C.ID as CODE,C.NAME,C.PARENTID, C.DESCRIPTION AS DESCR FROM CATEGORIES C WHERE C.MERCHANT_ID = '$MERCHANT' AND C.PARENTID IN ('".  implode("','", array_keys($cat))."') 
        AND C.OLO_VISIBLE=1 AND C.ID IN (SELECT P.CATEGORY FROM PRODUCTS P,PRODUCTS_MENUSESSIONSLOTS PM,PRODUCTS_CAT PC WHERE
        PC.PRODUCT=P.ID AND PC.MERCHANT_ID=P.MERCHANT_ID AND PC.ISOLO = 1 AND  
        PM.PRODUCT=P.ID AND PM.MERCHANT_ID=P.MERCHANT_ID AND 
        P.MERCHANT_ID = '$MERCHANT' AND C.ID=P.CATEGORY)
        ORDER BY C.OLO_LINENO";
  
  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $menu[$row['PARENTID']][$row['CODE']] = $row;
  }
  
  $q = "SELECT P.CODE as CODE, P.OLO_NAME as NAME, P.ATTRIBUTES as DESCR, P.ID as PID, P.CATEGORY 
          FROM 
        PRODUCTS P, PRODUCTS_MENUSESSIONSLOTS PM, PRODUCTS_CAT PC 
          WHERE 
        P.PRICESELL > 0 AND P.SPECIAL_TYPE IS NULL AND DELETED=0 AND PM.PRODUCT=P.ID 
        AND PM.MERCHANT_ID=P.MERCHANT_ID AND 
        PC.PRODUCT=P.ID AND PC.MERCHANT_ID=P.MERCHANT_ID AND PC.ISOLO = 1 AND  
        P.CATEGORY IN ('".  implode("','", array_keys($cat))."') AND P.MERCHANT_ID = '$MERCHANT'";
  
  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $menu[$row['CATEGORY']][$row['PID']] = $row;
  }
  return $menu;
}






























// type 1 = last 30d, 2 = 30d-60d


function getLast30DOrders($MERCHANT) {
  $dataOrders = array();
  $q = "SELECT
          T.ID, T.CREATEDDATE,
          SUM(TL.UNITS * TL.PRICE) AS TOTAL
        FROM
          TICKETS T 
          LEFT JOIN TICKETLINES TL ON (T.MERCHANT_ID=TL.MERCHANT_ID AND TL.LINETYPE = 0 AND T.ID=TL.TICKET AND TL.UNITS > 0)
        WHERE
          T.MERCHANT_ID = '".$MERCHANT."' AND
          T.CREATEDDATE BETWEEN DATE_ADD(NOW(), INTERVAL -30 DAY) AND NOW() AND
          T.TICKETTYPE = 0
        GROUP BY T.ID
        ORDER BY T.CREATEDDATE
       ";
  $res = mysql_query($q) or die(mysql_error());

  while ($row = mysql_fetch_assoc($res)) {
    $dataOrders[] = $row;
  }
  return $dataOrders;
}

function getMerchantData($MERCHANT) {
  $q = "SELECT
         IS_DELIVERY_AVAILABLE, IS_TAKE_OUT_AVAILABLE
        FROM
         MERCHANT
        WHERE ID='$MERCHANT'";
  $res = mysql_query($q) or die(mysql_error());
  while ($raw = mysql_fetch_assoc($res)) {
    $cat[] = $raw;
  }
  return $cat;
}

function getMerchantCuisine($MERCHANT) {
  $cus = array();
  $q = "SELECT
         CUISINE_ID
        FROM
         MERCHANT_CUISINE
        WHERE MERCHANT_ID='$MERCHANT'";
  $res = mysql_query($q) or die(mysql_error());
  while ($raw = mysql_fetch_assoc($res)) {
    $cus[] = $raw['CUISINE_ID'];
  }
  return $cus;
}

function getCategories($MERCHANT) {
  $cat = array();
  $q = "SELECT
         NAME
        FROM
         CATEGORIES
        WHERE MERCHANT_ID='$MERCHANT' AND SPECIAL_TYPE IS NULL";
  $res = mysql_query($q) or die(mysql_error());
  while ($raw = mysql_fetch_assoc($res)) {
    $cat[] = $raw['NAME'];
  }
  return $cat;
}



function getAllItems($MERCHANT) {
  $dataOrders = array();

  $q = "SELECT
          P.ID AS PRODUCT_ID, P.NAME, P.CODE, P.PRICESELL,
          SUM(TL.UNITS) AS TOTAL_SALE_COUNT,
          SUM(TL.UNITS * TL.PRICE) AS TOTAL,
          TL.CREATEDDATE
        FROM
          TICKETLINES TL
          LEFT JOIN PRODUCTS P ON TL.MERCHANT_ID = P.MERCHANT_ID AND TL.PRODUCT = P.ID
        WHERE
          TL.MERCHANT_ID = '".$MERCHANT."' AND
          TL.CREATEDDATE BETWEEN DATE_ADD(NOW(), INTERVAL -30 DAY) AND NOW() AND
          TL.UNITS > 0 AND
          TL.PRODUCT IS NOT NULL AND
          TL.LINETYPE = 0
        GROUP BY TL.PRODUCT
        ORDER BY CREATEDDATE
       ";

  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $dataOrders[$row['PRODUCT_ID']] = $row;
  }

  return $dataOrders;
}

function getOldOrderedItems($MERCHANT) {
  $dataOrders = array();

  $q = "SELECT
          TL.PRODUCT AS PRODUCT_ID
        FROM
          TICKETLINES TL
        WHERE
          TL.MERCHANT_ID = '".$MERCHANT."' AND
          TL.CREATEDDATE < '2012-12-01 00:00:00' AND
          TL.UNITS > 0 AND
          TL.PRODUCT IS NOT NULL AND
          TL.LINETYPE = 0
        GROUP BY TL.PRODUCT
       ";

  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $dataOrders[] = $row['PRODUCT_ID'];
  }

  return $dataOrders;
}

function getFirstOrderDates($MERCHANT) {
  $days = array();
  $prodIds = array();
  $q = "SELECT
          TL.PRODUCT AS PRODUCT_ID, CREATEDDATE, DATEDIFF(CURDATE(), CREATEDDATE) AS DAYS
        FROM
          TICKETLINES TL
        WHERE
          TL.MERCHANT_ID = '".$MERCHANT."' AND
          TL.CREATEDDATE BETWEEN '2010-01-01' AND NOW() AND
          TL.UNITS > 0 AND
          TL.PRODUCT IS NOT NULL AND
          TL.LINETYPE = 0
        ORDER BY DAYS
       ";

  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    if (!isset($days[$row['PRODUCT_ID']]['DAYS']) || ($days[$row['PRODUCT_ID']]['DAYS'] < $row['DAYS']) ) {
        $days[$row['PRODUCT_ID']]['DAYS'] = $row['DAYS'];
        $days[$row['PRODUCT_ID']]['LAST'] = $row['CREATEDDATE'];
        $prodIds[$row['PRODUCT_ID']] = $row['DAYS'];
    }
    if (isset($days[$row['PRODUCT_ID']]['COUNT'])) {
      $days[$row['PRODUCT_ID']]['COUNT'] = $days[$row['PRODUCT_ID']]['COUNT']+1;
    } else {
      $days[$row['PRODUCT_ID']]['COUNT'] = 1;
    }

  }

  return $prodIds;
}

function getProductCount($MERCHANT) {
  $q = "SELECT COUNT(*) AS PCOUNT FROM PRODUCTS WHERE MERCHANT_ID = '$MERCHANT' AND DELETED=0 AND SPECIAL_TYPE IS NULL";
  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $dataOrders = $row['PCOUNT'];
  }
  return $dataOrders;
}

function getSaleUnits($MERCHANT) {
  $q = "SELECT 
          count(*) as PCOUNT 
        FROM
          TICKETLINES TL
        WHERE
          TL.MERCHANT_ID = '".$MERCHANT."' AND
          TL.CREATEDDATE > '".$_SESSION['from']."' AND TL.CREATEDDATE <= '".$_SESSION['to']."' AND
          TL.UNITS > 0 AND
          TL.PRODUCT IS NOT NULL AND
          TL.LINETYPE = 0
       ";
  
  $res = mysql_query($q) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
    $dataOrders = $row['PCOUNT'];
  }
  return $dataOrders;
}
?>
